"""
消息推送数据CRUD操作
包含消息的增删改查和相关业务逻辑
"""
import json
from datetime import datetime, timedelta
from typing import List, Optional, Tuple, Dict, Any

from app.models.notification_models import (
	Notification, NotificationBase, NotificationSummary, NotificationFilter,
	NotificationType, NotificationPriority, NotificationStatus,
	create_notification_from_template
)
from app.schemas.notification_schemas import (
	NotificationCreateRequest, NotificationMarkReadRequest
)
from common.database import DatabaseService


# ---------- 基础CRUD操作 ----------

async def create_notification(db: DatabaseService, notification: NotificationBase) -> int:
    """
    创建单个消息通知

    Args:
        db: 数据库服务
        notification: 消息通知数据

    Returns:
        int: 新创建的消息ID
    """
    sql = """
          INSERT INTO base_notification (user_id, type, priority, title, content, data_json,
                                         read_flag, expire_time)
          VALUES (%s, %s, %s, %s, %s, %s, %s, %s) \
          """

    data_json_str = json.dumps(notification.data_json) if notification.data_json else None

    return db.insert(sql, (
        notification.user_id,
        notification.type.value,
        notification.priority.value,
        notification.title,
        notification.content,
        data_json_str,
        NotificationStatus.UNREAD.value,
        notification.expire_time
    ))


async def batch_create_notifications(
        db: DatabaseService,
        request: NotificationCreateRequest
) -> Tuple[int, List[int], List[int]]:
    """
    批量创建消息通知

    Args:
        db: 数据库服务
        request: 批量创建请求数据

    Returns:
        Tuple[int, List[int], List[int]]: (成功数量, 成功的消息ID列表, 失败的用户ID列表)
    """
    success_count = 0
    notification_ids = []
    failed_user_ids = []

    data_json_str = json.dumps(request.data_json) if request.data_json else None

    sql = """
          INSERT INTO base_notification (user_id, type, priority, title, content, data_json,
                                         read_flag, expire_time)
          VALUES (%s, %s, %s, %s, %s, %s, %s, %s) \
          """

    for user_id in request.user_ids:
        try:
            notification_id = db.insert(sql, (
                user_id,
                request.type.value,
                request.priority.value,
                request.title,
                request.content,
                data_json_str,
                NotificationStatus.UNREAD.value,
                request.expire_time
            ))
            notification_ids.append(notification_id)
            success_count += 1
        except Exception:
            failed_user_ids.append(user_id)

    return success_count, notification_ids, failed_user_ids


async def get_notification_by_id(db: DatabaseService, notification_id: int) -> Optional[Notification]:
    """
    根据ID获取消息通知

    Args:
        db: 数据库服务
        notification_id: 消息ID

    Returns:
        Notification: 消息通知对象，不存在则返回None
    """
    sql = """
          SELECT id,
                 user_id,
                 type,
                 priority,
                 title,
                 content,
                 data_json,
                 read_flag,
                 expire_time,
                 gmt_create
          FROM base_notification
          WHERE id = %s \
          """
    row = db.get_one(sql, (notification_id,))
    return _parse_notification_row(row) if row else None


async def get_notifications_by_user(
        db: DatabaseService,
        user_id: int,
        filter_params: Optional[NotificationFilter] = None,
        page: int = 1,
        page_size: int = 20
) -> Tuple[int, List[Notification]]:
    """
    获取用户的消息列表

    Args:
        db: 数据库服务
        user_id: 用户ID
        filter_params: 过滤条件
        page: 页码
        page_size: 每页大小

    Returns:
        Tuple[int, List[Notification]]: (总数, 消息列表)
    """
    # 构建WHERE条件
    where_conditions = ["user_id = %s"]
    params = [user_id]

    if filter_params:
        if filter_params.type:
            where_conditions.append("type = %s")
            params.append(filter_params.type.value)

        if filter_params.priority:
            where_conditions.append("priority = %s")
            params.append(filter_params.priority.value)

        if filter_params.read_flag is not None:
            where_conditions.append("read_flag = %s")
            params.append(filter_params.read_flag.value)

        if filter_params.start_time:
            where_conditions.append("gmt_create >= %s")
            params.append(filter_params.start_time)

        if filter_params.end_time:
            where_conditions.append("gmt_create <= %s")
            params.append(filter_params.end_time)

        if filter_params.keyword:
            where_conditions.append("(title LIKE %s OR content LIKE %s)")
            keyword_pattern = f"%{filter_params.keyword}%"
            params.extend([keyword_pattern, keyword_pattern])

        if not filter_params.include_expired:
            where_conditions.append("(expire_time IS NULL OR expire_time > %s)")
            params.append(datetime.now())

    where_clause = " AND ".join(where_conditions)

    # 查询总数
    count_sql = f"SELECT COUNT(*) as total FROM base_notification WHERE {where_clause}"
    count_row = db.get_one(count_sql, tuple(params))
    total = count_row['total'] if count_row else 0

    # 查询数据
    offset = (page - 1) * page_size
    data_sql = f"""
        SELECT id, user_id, type, priority, title, content, data_json,
               read_flag, expire_time, gmt_create
        FROM base_notification
        WHERE {where_clause}
        ORDER BY gmt_create DESC
        LIMIT %s OFFSET %s
    """
    params.extend([page_size, offset])

    rows = db.get_all(data_sql, tuple(params))
    notifications = [_parse_notification_row(row) for row in rows]

    return total, notifications


async def get_unread_notifications(
        db: DatabaseService,
        user_id: int,
        page: int = 1,
        page_size: int = 20
) -> Tuple[int, List[Notification]]:
    """
    获取用户未读消息列表

    Args:
        db: 数据库服务
        user_id: 用户ID
        page: 页码
        page_size: 每页大小

    Returns:
        Tuple[int, List[Notification]]: (总数, 未读消息列表)
    """
    filter_params = NotificationFilter(
        user_id=user_id,
        read_flag=NotificationStatus.UNREAD,
        include_expired=False
    )
    return await get_notifications_by_user(db, user_id, filter_params, page, page_size)


# 兼容原有接口
async def get_unread(user_id: int, page: int = 1, page_size: int = 20) -> Tuple[int, List[Dict]]:
    """
    兼容原有接口的获取未读消息函数
    """
    from common.database import get_db

    with get_db() as db:
        total, notifications = await get_unread_notifications(db, user_id, page, page_size)
        # 转换为字典格式
        items = []
        for notification in notifications:
            item = {
                "id": notification.id,
                "type": notification.type.value,
                "priority": notification.priority.value,
                "title": notification.title,
                "content": notification.content,
                "data_json": notification.data_json,
                "gmt_create": notification.gmt_create
            }
            items.append(item)
        return total, items


async def mark_notification_as_read(
        db: DatabaseService,
        notification_id: int,
        user_id: int
) -> bool:
    """
    标记单个消息为已读

    Args:
        db: 数据库服务
        notification_id: 消息ID
        user_id: 用户ID（验证权限）

    Returns:
        bool: 标记成功返回True，消息不存在或无权限返回False
    """
    sql = """
          UPDATE base_notification
          SET read_flag = %s
          WHERE id = %s
            AND user_id = %s
            AND read_flag = %s \
          """
    affected_rows = db.update(sql, (
        NotificationStatus.READ.value,
        notification_id,
        user_id,
        NotificationStatus.UNREAD.value
    ))
    return affected_rows > 0


async def batch_mark_as_read(
        db: DatabaseService,
        request: NotificationMarkReadRequest,
        user_id: int
) -> Tuple[int, List[int]]:
    """
    批量标记消息为已读

    Args:
        db: 数据库服务
        request: 批量标记请求
        user_id: 用户ID

    Returns:
        Tuple[int, List[int]]: (成功数量, 失败的消息ID列表)
    """
    success_count = 0
    failed_ids = []

    for notification_id in request.notification_ids:
        try:
            if await mark_notification_as_read(db, notification_id, user_id):
                success_count += 1
            else:
                failed_ids.append(notification_id)
        except Exception:
            failed_ids.append(notification_id)

    return success_count, failed_ids


# 兼容原有接口
async def mark_as_read(notification_id: int, user_id: int) -> bool:
    """
    兼容原有接口的标记已读函数
    """
    from common.database import get_db

    with get_db() as db:
        return await mark_notification_as_read(db, notification_id, user_id)


async def mark_all_as_read(db: DatabaseService, user_id: int) -> int:
    """
    标记用户所有未读消息为已读

    Args:
        db: 数据库服务
        user_id: 用户ID

    Returns:
        int: 标记的消息数量
    """
    sql = """
          UPDATE base_notification
          SET read_flag = %s
          WHERE user_id = %s
            AND read_flag = %s \
          """
    return db.update(sql, (
        NotificationStatus.READ.value,
        user_id,
        NotificationStatus.UNREAD.value
    ))


async def delete_notification(
        db: DatabaseService,
        notification_id: int,
        user_id: int
) -> bool:
    """
    删除消息通知

    Args:
        db: 数据库服务
        notification_id: 消息ID
        user_id: 用户ID（验证权限）

    Returns:
        bool: 删除成功返回True，消息不存在或无权限返回False
    """
    sql = "DELETE FROM base_notification WHERE id = %s AND user_id = %s"
    affected_rows = db.delete(sql, (notification_id, user_id))
    return affected_rows > 0


async def batch_delete_notifications(
        db: DatabaseService,
        notification_ids: List[int],
        user_id: int
) -> Tuple[int, List[int]]:
    """
    批量删除消息通知

    Args:
        db: 数据库服务
        notification_ids: 消息ID列表
        user_id: 用户ID

    Returns:
        Tuple[int, List[int]]: (成功数量, 失败的消息ID列表)
    """
    success_count = 0
    failed_ids = []

    for notification_id in notification_ids:
        try:
            if await delete_notification(db, notification_id, user_id):
                success_count += 1
            else:
                failed_ids.append(notification_id)
        except Exception:
            failed_ids.append(notification_id)

    return success_count, failed_ids


async def get_notification_summary(db: DatabaseService, user_id: int) -> NotificationSummary:
    """
    获取用户消息摘要

    Args:
        db: 数据库服务
        user_id: 用户ID

    Returns:
        NotificationSummary: 消息摘要信息
    """
    # 基础统计
    basic_sql = """
                SELECT COUNT(*)                                                                       as total_count,
                       SUM(CASE WHEN read_flag = 0 THEN 1 ELSE 0 END)                                 as unread_count,
                       SUM(CASE WHEN priority = 'URGENT' THEN 1 ELSE 0 END)                           as urgent_count,
                       SUM(CASE WHEN expire_time IS NOT NULL AND expire_time <= %s THEN 1 ELSE 0 END) as expired_count
                FROM base_notification
                WHERE user_id = %s \
                """
    basic_row = db.get_one(basic_sql, (datetime.now(), user_id))

    # 按类型统计
    type_sql = """
               SELECT type, COUNT(*) as count
               FROM base_notification
               WHERE user_id = %s
               GROUP BY type \
               """
    type_rows = db.get_all(type_sql, (user_id,))
    type_counts = {row['type']: row['count'] for row in type_rows}

    return NotificationSummary(
        total_count=basic_row['total_count'] if basic_row else 0,
        unread_count=basic_row['unread_count'] if basic_row else 0,
        urgent_count=basic_row['urgent_count'] if basic_row else 0,
        expired_count=basic_row['expired_count'] if basic_row else 0,
        system_count=type_counts.get('SYSTEM', 0),
        alarm_count=type_counts.get('ALARM', 0),
        device_count=type_counts.get('DEVICE', 0),
        work_order_count=type_counts.get('WORK_ORDER', 0)
    )


async def create_notification_from_template_key(
        db: DatabaseService,
        template_key: str,
        user_ids: List[int],
        variables: Dict[str, Any],
        data_json: Optional[Dict[str, Any]] = None,
        expire_time: Optional[datetime] = None
) -> Tuple[int, List[int], List[int]]:
    """
    使用模板创建消息通知

    Args:
        db: 数据库服务
        template_key: 模板键名
        user_ids: 接收用户ID列表
        variables: 模板变量值
        data_json: 扩展数据
        expire_time: 过期时间

    Returns:
        Tuple[int, List[int], List[int]]: (成功数量, 成功的消息ID列表, 失败的用户ID列表)
    """
    success_count = 0
    notification_ids = []
    failed_user_ids = []

    for user_id in user_ids:
        try:
            notification = create_notification_from_template(
                template_key, user_id, variables, data_json, expire_time
            )
            if notification:
                notification_id = await create_notification(db, notification)
                notification_ids.append(notification_id)
                success_count += 1
            else:
                failed_user_ids.append(user_id)
        except Exception:
            failed_user_ids.append(user_id)

    return success_count, notification_ids, failed_user_ids


async def broadcast_notification(
        db: DatabaseService,
        notification: NotificationBase,
        role_filter: Optional[List[str]] = None
) -> Tuple[int, List[int]]:
    """
    广播消息给所有用户或指定角色用户

    Args:
        db: 数据库服务
        notification: 消息通知数据
        role_filter: 角色筛选，只发送给指定角色的用户

    Returns:
        Tuple[int, List[int]]: (成功数量, 成功的消息ID列表)
    """
    # 获取目标用户列表
    if role_filter:
        placeholders = ','.join(['%s'] * len(role_filter))
        user_sql = f"""
            SELECT id FROM base_user
            WHERE disabled = 0 AND role IN ({placeholders})
        """
        user_rows = db.get_all(user_sql, tuple(role_filter))
    else:
        user_sql = "SELECT id FROM base_user WHERE disabled = 0"
        user_rows = db.get_all(user_sql)

    user_ids = [row['id'] for row in user_rows]

    # 批量创建消息
    success_count = 0
    notification_ids = []

    data_json_str = json.dumps(notification.data_json) if notification.data_json else None

    sql = """
          INSERT INTO base_notification (user_id, type, priority, title, content, data_json,
                                         read_flag, expire_time)
          VALUES (%s, %s, %s, %s, %s, %s, %s, %s) \
          """

    for user_id in user_ids:
        try:
            notification_id = db.insert(sql, (
                user_id,
                notification.type.value,
                notification.priority.value,
                notification.title,
                notification.content,
                data_json_str,
                NotificationStatus.UNREAD.value,
                notification.expire_time
            ))
            notification_ids.append(notification_id)
            success_count += 1
        except Exception:
            continue

    return success_count, notification_ids


# 兼容原有接口
async def insert_notification(user_id: int, payload: dict) -> int:
    """
    兼容原有接口的插入消息函数
    """
    from common.database import get_db

    # 从payload提取消息信息
    notification = NotificationBase(
        user_id=user_id,
        type=NotificationType(payload.get('type', 'SYSTEM')),
        priority=NotificationPriority(payload.get('priority', 'NORMAL')),
        title=payload.get('title', '系统消息'),
        content=payload.get('content'),
        data_json=payload.get('data')
    )

    with get_db() as db:
        return await create_notification(db, notification)


async def cleanup_expired_notifications(db: DatabaseService, days: int = 30) -> int:
    """
    清理过期的消息通知

    Args:
        db: 数据库服务
        days: 清理多少天前的过期消息

    Returns:
        int: 清理的消息数量
    """
    cutoff_time = datetime.now() - timedelta(days=days)
    sql = """
          DELETE
          FROM base_notification
          WHERE expire_time IS NOT NULL
            AND expire_time <= %s \
          """
    return db.delete(sql, (cutoff_time,))


async def get_notification_statistics(
        db: DatabaseService,
        start_time: Optional[datetime] = None,
        end_time: Optional[datetime] = None,
        user_id: Optional[int] = None
) -> Dict[str, Any]:
    """
    获取消息统计信息

    Args:
        db: 数据库服务
        start_time: 开始时间
        end_time: 结束时间
        user_id: 指定用户ID（可选）

    Returns:
        Dict[str, Any]: 统计信息
    """
    # 构建WHERE条件
    where_conditions = []
    params = []

    if start_time:
        where_conditions.append("gmt_create >= %s")
        params.append(start_time)

    if end_time:
        where_conditions.append("gmt_create <= %s")
        params.append(end_time)

    if user_id:
        where_conditions.append("user_id = %s")
        params.append(user_id)

    where_clause = "WHERE " + " AND ".join(where_conditions) if where_conditions else ""

    # 基础统计
    basic_sql = f"""
        SELECT
            COUNT(*) as total_count,
            SUM(CASE WHEN read_flag = 1 THEN 1 ELSE 0 END) as read_count,
            SUM(CASE WHEN read_flag = 0 THEN 1 ELSE 0 END) as unread_count,
            SUM(CASE WHEN priority = 'URGENT' THEN 1 ELSE 0 END) as urgent_count,
            SUM(CASE WHEN expire_time IS NOT NULL AND expire_time <= %s THEN 1 ELSE 0 END) as expired_count
        FROM base_notification
        {where_clause}
    """
    params_with_now = params + [datetime.now()]
    basic_row = db.get_one(basic_sql, tuple(params_with_now))

    # 按类型分组统计
    type_sql = f"""
        SELECT type, COUNT(*) as count
        FROM base_notification
        {where_clause}
        GROUP BY type
    """
    type_rows = db.get_all(type_sql, tuple(params))

    return {
        "total_count": basic_row['total_count'] if basic_row else 0,
        "read_count": basic_row['read_count'] if basic_row else 0,
        "unread_count": basic_row['unread_count'] if basic_row else 0,
        "urgent_count": basic_row['urgent_count'] if basic_row else 0,
        "expired_count": basic_row['expired_count'] if basic_row else 0,
        "groups": [{"type": row['type'], "count": row['count']} for row in type_rows]
    }


def _parse_notification_row(row: Dict[str, Any]) -> Notification:
    """
    解析数据库行数据为Notification模型数据

    Args:
        row: 数据库查询结果行

    Returns:
        Notification: 解析后的消息对象
    """
    parsed = dict(row)

    # 解析JSON字段
    if 'data_json' in parsed and parsed['data_json']:
        try:
            parsed['data_json'] = json.loads(parsed['data_json'])
        except json.JSONDecodeError:
            parsed['data_json'] = None

    # 转换枚举类型
    if 'type' in parsed:
        parsed['type'] = NotificationType(parsed['type'])

    if 'priority' in parsed:
        parsed['priority'] = NotificationPriority(parsed['priority'])

    if 'read_flag' in parsed:
        parsed['read_flag'] = NotificationStatus(parsed['read_flag'])

    return Notification(**parsed)
